package com.ecable.daoImpl;

import java.io.FileOutputStream;
import java.io.OutputStream;
import java.sql.Blob;
import java.sql.CallableStatement;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Connection;
import java.sql.Types;
import java.util.Vector;

import com.ecable.bean.BillsTO; //import com.ecable.bean.ConnectionTO;
import com.ecable.bean.ComplaintsTO;
import com.ecable.bean.PackagesTO;
import com.ecable.bean.ProfileTO;
import com.ecable.dao.AbstractDataAccessObject;
import com.ecable.daoI.ConnectionDaoI;
import com.ecable.util.DateWrapper;
import com.ecable.util.LoggerManager;

public class ConnectionDaoImpl extends AbstractDataAccessObject implements
		ConnectionDaoI {
	public Connection con;
	public PreparedStatement pstmt;

	public ConnectionDaoImpl() {
		con = getConnection();
	}

	public boolean addConnection(com.ecable.bean.ConnectionTO conn, BillsTO bill) {

		boolean flag = false;
		String connplan = conn.getConnplan();
		System.out.println(connplan);
		String bankname = bill.getBankname();
		System.out.println(bankname);
		float bankamount = bill.getAmount();
		System.out.println(bankamount);
		String bankdd = bill.getBankdd();
		System.out.println(bankdd);
		int userid = conn.getUserid();
		System.out.println(userid);
		try {
			CallableStatement cs = con
					.prepareCall("{call addconnection(?,?,?,?,?,?,?)}");
			cs.setInt(1, userid);
			cs.setInt(2, Integer.parseInt(connplan));
			cs.setInt(3, userid);
			cs.setFloat(4, bankamount);
			cs.setString(5, bankname);
			cs.setString(6, bankdd);
			cs.registerOutParameter(7, Types.INTEGER);
			cs.execute();
			int b = cs.getInt(7);
			System.out.println(b);
			if (b > 0) {
				con.commit();
				flag = true;
			} else {
				flag = false;
				con.rollback();
			}
		} catch (SQLException e) {
			System.out.println(e);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e);
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				System.out.println(se);
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e);
			}
		}
		return flag;
	}

	public Vector<com.ecable.bean.ConnectionTO> getConnectionStatus(
			String userid) {

		Vector<com.ecable.bean.ConnectionTO> v = new Vector<com.ecable.bean.ConnectionTO>();

		com.ecable.bean.ConnectionTO conn = null;
		v.clear();

		try {

			pstmt = con
					.prepareStatement("select * from Connection where userid=? ORDER BY 1");

			pstmt.setString(1, userid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				conn = new com.ecable.bean.ConnectionTO();
				conn.setConnid(rs.getInt(1));
				conn.setConnplan(rs.getString(3));
				conn.setConndate(DateWrapper.parseDate(rs.getDate(4)));
				conn.setStatus(rs.getString(5));
				System.out.println(rs.getString(5));

				v.add(conn);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public boolean acceptCoustomerBill(int userid, int billid) {

		boolean flag = false;

		System.out.println(userid);
		try {

			pstmt = con
					.prepareStatement("update bills set billstatus='Active' where userid=? and billid=?");

			pstmt.setInt(1, userid);
			pstmt.setInt(2, billid);
			int b = pstmt.executeUpdate();
			if (b > 0) {
				con.commit();
				flag = true;
			} else {
				flag = false;
				con.rollback();
			}
		} catch (SQLException e) {
			System.out.println(e);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e);
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
				System.out.println(se);
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e);
			}
		}
		return flag;
	}

	public boolean payBill(BillsTO bill) {
		boolean flag = false;
		int userid = bill.getUserid();
		Float billamount = bill.getAmount();
		String bankname = bill.getBankname();
		int bankdd = Integer.parseInt(bill.getBankdd());
		int connid = bill.getConnid();
		System.out.println(userid);
		System.out.println(bankname);
		System.out.println(billamount);
		System.out.println(bankdd);
		try {
			pstmt = con
					.prepareStatement("insert into Bills values((select nvl(max(billid),1000)+1 from bills),?,sysdate,?,?,?,'Request',?)");
			pstmt.setInt(1, userid);
			pstmt.setFloat(2, billamount);
			pstmt.setString(3, bankname);
			pstmt.setInt(4, bankdd);
			pstmt.setInt(5, connid);
			int i = pstmt.executeUpdate();
			if (i > 0) {
				flag = true;
			} else
				flag = false;
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
			System.out.println(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return flag;
	}

	public Vector<com.ecable.bean.BillsTO> getBillsStatus(String userid) {

		Vector<com.ecable.bean.BillsTO> v = new Vector<com.ecable.bean.BillsTO>();

		com.ecable.bean.BillsTO bill = null;
		v.clear();
		try {
			pstmt = con
					.prepareStatement("select * from Bills where userid=? ORDER BY 1");
			pstmt.setString(1, userid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				bill = new com.ecable.bean.BillsTO();
				bill.setBillid(rs.getInt(1));
				bill.setUserid(rs.getInt(2));
				bill.setBilldate(DateWrapper.parseDate(rs.getDate(3)));
				bill.setAmount(rs.getFloat(4));
				bill.setBankname(rs.getString(5));
				bill.setBankdd(rs.getString(6));
				bill.setBillstatus(rs.getString(7));
				v.add(bill);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public Vector<ProfileTO> getOperatorConnStatus(String state,
			String district, String city) {

		Vector<ProfileTO> v = new Vector<ProfileTO>();

		ProfileTO p = null;
		v.clear();

		try {

			pstmt = con
					.prepareStatement("select u.firstname,c.connplan,c.conndate,c.connstatus,"
							+ "a.city,a.street,a.houseno,p.phoneno,c.connid "
							+ "from userdetails u,connection c,addresses a,phones p "
							+ "where u.userid=a.userid and u.userid=p.userid and "
							+ "u.userid=c.userid and a.userid=p.userid and "
							+ "p.userid=c.userid and a.state=? and a.district=? and a.city=? ORDER BY 1");

			pstmt.setString(1, state);
			pstmt.setString(2, district);
			pstmt.setString(3, city);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				p = new ProfileTO();
				p.setFirstname(rs.getString(1));
				p.setConnplan(rs.getString(2));
				p.setConndate(DateWrapper.parseDate(rs.getDate(3)));
				p.setConnstatus(rs.getString(4));
				p.setCity(rs.getString(5));
				p.setStreet(rs.getString(6));
				p.setHno(rs.getString(7));
				p.setPhone(rs.getString(8));
				p.setConnid(rs.getInt(9));
				System.out.println(rs.getString(8));

				v.add(p);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public Vector<ProfileTO> getOperatorBillsStatus(String state,
			String district, String city) {

		Vector<ProfileTO> v = new Vector<ProfileTO>();

		ProfileTO p = null;
		v.clear();
		System.out.println("-------------------" + state);
		System.out.println(district);
		System.out.println(city);
		try {

			pstmt = con
					.prepareStatement("select u.userid,u.firstname,c.connplan,"
							+ "b.billdate,b.bankname,b.billamount,b.bankdd,b.billstatus,"
							+ "a.city,a.street,a.houseno,p.phoneno,b.billid,c.connid "
							+ "from bills b,userdetails u,connection c,"
							+ "addresses a,phones p "
							+ "where  b.userid=u.userid and b.userid=a.userid "
							+ "and b.userid=c.userid and b.userid=p.userid  "
							+ "and u.userid=a.userid and u.userid=p.userid  "
							+ "and u.userid=c.userid and c.userid=a.userid "
							+ "and c.userid=p.userid   and a.userid=p.userid  "
							+ "and b.connid=c.connid and a.state=? and a.district=? and a.city=? ORDER BY 1");

			pstmt.setString(1, state);
			pstmt.setString(2, district);
			pstmt.setString(3, city);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				p = new ProfileTO();
				p.setUserid(Integer.parseInt(rs.getString(1)));
				p.setFirstname(rs.getString(2));
				p.setConnplan(rs.getString(3));
				p.setBilldate(DateWrapper.parseDate(rs.getDate(4)));
				p.setBankname(rs.getString(5));
				p.setBillamount(rs.getFloat(6));
				p.setBankdd(rs.getString(7));
				p.setBillstatus(rs.getString(8));
				p.setCity(rs.getString(9));
				p.setStreet(rs.getString(10));
				p.setHno(rs.getString(11));
				p.setPhone(rs.getString(12));
				p.setBillid(rs.getString(13));
				p.setConnid(rs.getInt(14));

				v.add(p);
			}
		} catch (SQLException se) {
			System.out.println(se);
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public boolean addComplaint(ComplaintsTO cmp) {

		boolean flag = false;
		int userid = cmp.getUserid();
		String Cmpdiscription = cmp.getCmpdiscription();

		try {

			pstmt = con
					.prepareStatement("insert into Complaints(userid,cmpid,cmpdiscription,cmpdate,cmpreply,cmpstatus) values(?,(select nvl(max(cmpid),100)+1 from complaints),?,sysdate,'NoReplay','Request')");

			pstmt.setInt(1, userid);

			pstmt.setString(2, Cmpdiscription);

			int i = pstmt.executeUpdate();
			if (i > 0) {
				flag = true;
			} else
				flag = false;
		} catch (SQLException e) {
			System.out.println(e);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e);
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
				System.out.println(se);
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e);
			}
		}
		return flag;
	}

	public Vector<com.ecable.bean.ComplaintsTO> getCustomerComplaints(
			String userid) {

		Vector<com.ecable.bean.ComplaintsTO> v = new Vector<com.ecable.bean.ComplaintsTO>();
		com.ecable.bean.ComplaintsTO cmp = null;
		v.clear();

		try {

			pstmt = con
					.prepareStatement("select * from Complaints where userid=? ORDER BY 1");
			pstmt.setString(1, userid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				cmp = new com.ecable.bean.ComplaintsTO();
				cmp.setUserid(rs.getInt(1));
				cmp.setCmpid(rs.getInt(2));
				cmp.setCmpdiscription(rs.getString(3));
				cmp.setCmpdate(rs.getString(4));
				cmp.setCmpreply(rs.getString(5));
				cmp.setCmpstatus(rs.getString(6));
				System.out.println(rs.getString(5));

				v.add(cmp);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public boolean deleteCustomerComplaints(int userid, int cmpid) {

		boolean flag = false;

		System.out.println(userid);

		try {

			pstmt = con
					.prepareStatement("delete from complaints where userid=? and cmpid=?");
			pstmt.setInt(1, userid);
			pstmt.setInt(2, cmpid);
			int b = pstmt.executeUpdate();
			if (b > 0) {
				con.commit();
				flag = true;
			} else {
				flag = false;
				con.rollback();
			}
		} catch (SQLException e) {
			System.out.println(e);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e);
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
				System.out.println(se);
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e);
			}
		}
		return flag;
	}

	public Vector<ProfileTO> viewOperCustomerComplaints(String state,
			String district, String city) {

		Vector<ProfileTO> v = new Vector<ProfileTO>();

		ProfileTO p = null;
		v.clear();

		try {

			pstmt = con
					.prepareStatement("select a.userid,u.firstname,c.cmpdiscription,c.cmpdate,c.cmpstatus,c.cmpreply,c.cmpid from addresses a,userdetails u,complaints c  where a.userid=u.userid and a.userid=c.userid and a.userid=u.userid and a.state=? and a.district=? and a.city=? ORDER BY 1");

			pstmt.setString(1, state);
			pstmt.setString(2, district);
			pstmt.setString(3, city);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				p = new ProfileTO();
				p.setUserid(Integer.parseInt(rs.getString(1)));
				p.setFirstname(rs.getString(2));
				p.setCmpdicription(rs.getString(3));
				p.setCmpdate(rs.getString(4));
				p.setCmpstatus(rs.getString(5));
				p.setCmpreply(rs.getString(6));
				p.setCmpid(rs.getInt(7));
				v.add(p);
			}
		} catch (SQLException se) {
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return v;
	}

	public boolean replyComplaints(String ComplaintDesc, int userid, int cmpid) {

		boolean flag = false;

		System.out.println(ComplaintDesc);
		System.out.println(userid);
		try {

			pstmt = con
					.prepareStatement("update complaints set cmpreply=? where userid=? and cmpid=?");
			pstmt.setString(1, ComplaintDesc);
			pstmt.setInt(2, userid);
			pstmt.setInt(3, cmpid);
			int b = pstmt.executeUpdate();
			if (b > 0) {
				con.commit();
				flag = true;
			} else {
				flag = false;
				con.rollback();
			}
		} catch (SQLException e) {
			System.out.println(e);
		} catch (Exception e) {
			e.printStackTrace();
			System.out.println(e);
			flag = false;
			try {
				con.rollback();
			} catch (SQLException se) {
				se.printStackTrace();
				System.out.println(se);
			}
		}

		finally {
			try {
				if (con != null)
					con.close();
			} catch (SQLException e) {
				System.out.println(e);
			}
		}
		return flag;
	}

	public boolean updateOperatorStatus(int connid) {
		boolean flag = false;
		try {
			System.out.println(connid);
			pstmt = con
					.prepareStatement("update connection set connstatus='Active' where connid=?");
			pstmt.setInt(1, connid);
			int i = pstmt.executeUpdate();
			if (i > 0) {
				flag = true;
			}
		} catch (SQLException se) {
			System.out.println(se);
			LoggerManager.writeLogWarning(se);
		} catch (Exception e) {
			System.out.println(e);
			LoggerManager.writeLogWarning(e);
		} finally {
			try {
				con.close();
			} catch (SQLException se) {
				LoggerManager.writeLogWarning(se);
			}
		}
		return flag;
	}
}